
create  view dbo.vw_flujo_efectivo (
	cvedesarrollo,
	registro,
	anio,
	total
) as 
select	iddesarrollo,
	mes,
	anio,
	sum( total ) as total
from	(
SELECT iddesarrollo,
	1 as mes,
	year( getDate() ) as anio, 
	sum( DetVenta.Mensualidad ) as total
FROM 	detVenta, lotes 
WHERE 	detVenta.Cvelote=lotes.idlote and 
  	lotes.staventa='C' and
	( year( DetVenta.Vencimiento ) < year( getDate() ) or 
	  ( year( DetVenta.Vencimiento ) = year( getDate() ) and month( DetVenta.Vencimiento ) < month( getDate() ) 
	  )
	)
group by iddesarrollo
union
SELECT 	iddesarrollo,
	1 as mes,
	year( getDate() ) as anio,
	sum( Importe ) as total
FROM 	Enganches, lotes 
WHERE 	Enganches.[CveLote] = lotes.[IdLote] and 
	lotes.StaVenta='C' and
	nopago = 1 and
	tipo in ('C','E') and
	( year( Enganches.fechavento ) < year( getDate() ) or 
	  ( year( Enganches.fechavento ) = year( getDate() ) and month( Enganches.fechavento ) < month( getDate() ) 
	  )
	)
group by iddesarrollo
union
SELECT 	iddesarrollo,
	1 as mes,
	year( getDate() ) as anio,
	sum( importe ) as total
FROM 	Enganches, lotes 
WHERE 	Enganches.[CveLote] = lotes.[IdLote] and 
	lotes.StaVenta='C' and
	tipo = 'D' and
	( year( Enganches.fechavento ) < year( getDate() ) or 
	  ( year( Enganches.fechavento ) = year( getDate() ) and month( Enganches.fechavento ) < month( getDate() ) 
	  )
	)
group by iddesarrollo
) as tabla
group by iddesarrollo,
	 mes,
	 anio
union
select	iddesarrollo,
	mes,
	anio,
	sum( total ) as total
from	(
SELECT iddesarrollo,
	2 as mes,
	year( getDate() ) as anio, 
	sum( DetVenta.Mensualidad ) as total
FROM 	detVenta,lotes 
WHERE 	detVenta.CveLote=lotes.IdLote and 
  	lotes.staventa='C' and
	year( DetVenta.Vencimiento ) = year( getDate() ) and
	month( DetVenta.Vencimiento ) >= month( getDate() )
group by iddesarrollo
union
SELECT 	iddesarrollo,
	2 as mes,
	year( getDate() ) as anio,
	sum( Importe ) as total
FROM 	Enganches, lotes 
WHERE 	Enganches.[CveLote] = lotes.[IdLote] and 
	lotes.StaVenta='C' and
	nopago = 1 and
	tipo in ('C','E') and
	year( Enganches.fechavento ) = year( getDate() ) and
	month( Enganches.fechavento ) >= month( getDate() )
group by iddesarrollo
union
SELECT 	iddesarrollo,
	2 as mes,
	year( getDate() ) as anio,
	sum( importe ) as total
FROM 	Enganches, lotes 
WHERE 	Enganches.[CveLote] = lotes.[IdLote] and 
	lotes.StaVenta='C' and
	tipo = 'D' and
	year( Enganches.fechavento ) = year( getDate() ) and
	month( Enganches.fechavento ) >= month( getDate() )
group by iddesarrollo
) as tabla
group by iddesarrollo,
	 mes,
	 anio
union
select	iddesarrollo,
	mes,
	anio,
	sum( total ) as total
from	(
SELECT iddesarrollo,
	1 as mes,
	year( DetVenta.Vencimiento ) as anio, 
	sum( DetVenta.Mensualidad ) as total
FROM 	detVenta,lotes 
WHERE 	detVenta.Cvelote=lotes.IdLote and 
  	lotes.staventa='C' and
	year( DetVenta.Vencimiento ) > year( getDate() )
group by iddesarrollo, 
	 year(  DetVenta.Vencimiento )
union
SELECT 	iddesarrollo,
	1 as mes,
	year( Enganches.fechavento ) as anio,
	sum( Importe ) as total
FROM 	Enganches, lotes 
WHERE 	Enganches.[CveLote] = lotes.[IdLote] and 
	lotes.StaVenta='C' and
	nopago = 1 and
	tipo in ('C','E') and
	year( Enganches.fechavento ) > year( getDate() )
group by iddesarrollo, 
	 year( Enganches.fechavento )
union
SELECT 	iddesarrollo,
	1 as mes,
	year( Enganches.fechavento ) as anio,
	sum( importe ) as total
FROM 	Enganches, lotes 
WHERE 	Enganches.[CveLote] = lotes.[IdLote] and 
	lotes.StaVenta='C' and
	tipo = 'D' and
	year( Enganches.fechavento ) > year( getDate() )
group by iddesarrollo, 
	 year( Enganches.fechavento )
) as tabla
group by iddesarrollo,
	 mes,
	 anio
GO
